package untiring07.j2ee.basic.projectv8.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import untiring07.j2ee.basic.projectv5.model.BoardVO;
import untiring07.j2ee.basic.util.jdbc.MariaDB;

public class BoardDAO extends untiring07.j2ee.basic.projectv7.dao.BoardDAO {
		private static String findBaseSQL ="select bdno, title, uid, wdate, reades "
										+"from boardv4 ";
										//"where ? like ? "
		private static String findOrderSQL ="order by bdno desc limit ? ,?";
		
		public static List<BoardVO> listBoard(int startno, String ftype, String fkey){
			Connection conn =null;
			PreparedStatement pstmt = null;
			ResultSet rs = null;
			List<BoardVO> result=new ArrayList<>();
			
			String whereSQL = " where "+ftype+" like '%"+fkey+"%' ";
			String SQL = findBaseSQL+findOrderSQL;
			
			if(!ftype.equals("") && !fkey.equals("")){
			SQL = findBaseSQL+ whereSQL+findOrderSQL;
			}
			//동적으로 생성된SQL 문 확인
			// 검색어를 입력하지 않으면 : 기본SQL문 사용
			// 검색어를 입력하면           : 검색전용 SQL 문 사용
			System.out.println(SQL);
			try{
				conn = MariaDB.openConn();
				pstmt = conn.prepareStatement(SQL);
				pstmt.setInt(1, startno);
				pstmt.setInt(2, perPage);
				rs = pstmt.executeQuery();
				while ( rs.next() ) {
					BoardVO b=new BoardVO();
						b.setBdno( rs.getString(1) );
						b.setTitle( rs.getString(2) );
						b.setUid( rs.getString(3) );
						b.setWdate( rs.getString(4) );
						b.setReades( rs.getString(5) );
					result.add(b);
				}
				
			}catch(Exception ex){
				ex.printStackTrace();
			}finally{
				MariaDB.closeConn(conn, pstmt, rs);
			}
			
			return result;
		}
		public static int totalBoard(String ftype, String fkey){
			Connection conn = null;
			PreparedStatement pstmt = null;
			ResultSet rs = null;
			int result = 0;
			String SQL = totalSQL;
			
			if(!ftype.equals("") && !fkey.equals("")){
				SQL += " where "+ftype+" like '%"+fkey+"%'";
			}
			System.out.println(SQL);
			
			try{
				conn=MariaDB.openConn();
				pstmt=conn.prepareStatement(SQL);
				rs=pstmt.executeQuery();
				if(rs.next()){
					result = rs.getInt(1);
				}
			}catch(Exception ex){
				ex.printStackTrace();
			}finally{
				MariaDB.closeConn(conn, pstmt, rs);
			}return result;
		}
		
		
	
}
